--題目1（10分）：使用PowerDesigner，设计可以使用的SqlServer数据库物理模型，要求中文名称和英文名称清楚（英文不知道的可以借助翻译软件），表关系准确；利用设计好的模型生成sql语句，创建一个名为OADatabase的数据库，备用；
--答：

create database OADatabase
go
use OADatabase
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DepartmentInfo') and o.name = 'FK_DEPARTME_REFERENCE_STAFFINF')
alter table DepartmentInfo
   drop constraint FK_DEPARTME_REFERENCE_STAFFINF
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DepartmentInfo') and o.name = 'FK_DEPARTME_REFERENCE_POSTINFO')
alter table DepartmentInfo
   drop constraint FK_DEPARTME_REFERENCE_POSTINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DepartmentInfo') and o.name = 'FK_DEPARTME_REFERENCE_SALESMAN')
alter table DepartmentInfo
   drop constraint FK_DEPARTME_REFERENCE_SALESMAN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('SalesmanInfo') and o.name = 'FK_SALESMAN_REFERENCE_POSTINFO')
alter table SalesmanInfo
   drop constraint FK_SALESMAN_REFERENCE_POSTINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('StaffInfo') and o.name = 'FK_STAFFINF_REFERENCE_POSTINFO')
alter table StaffInfo
   drop constraint FK_STAFFINF_REFERENCE_POSTINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('StaffInfo') and o.name = 'FK_STAFFINF_REFERENCE_SALESMAN')
alter table StaffInfo
   drop constraint FK_STAFFINF_REFERENCE_SALESMAN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('UserInfo') and o.name = 'FK_USERINFO_REFERENCE_USERAPPL')
alter table UserInfo
   drop constraint FK_USERINFO_REFERENCE_USERAPPL
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('company') and o.name = 'FK_COMPANY_REFERENCE_DEPARTME')
alter table company
   drop constraint FK_COMPANY_REFERENCE_DEPARTME
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DepartmentInfo')
            and   type = 'U')
   drop table DepartmentInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PostInfo')
            and   type = 'U')
   drop table PostInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SalesmanInfo')
            and   type = 'U')
   drop table SalesmanInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('StaffInfo')
            and   type = 'U')
   drop table StaffInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserApplyInfo')
            and   type = 'U')
   drop table UserApplyInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserInfo')
            and   type = 'U')
   drop table UserInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('company')
            and   type = 'U')
   drop table company
go

/*==============================================================*/
/* Table: DepartmentInfo                                        */
/*==============================================================*/
create table DepartmentInfo (
   DNO                  char(10)             not null,
   Wno                  char(10)             null,
   DNAME                char(10)             null,
   Supervisor           char(10)             null,
   Phone                char(10)             null,
   Emill                char(10)             null,
   constraint PK_DEPARTMENTINFO primary key (DNO)
)
go

/*==============================================================*/
/* Table: PostInfo                                              */
/*==============================================================*/
create table PostInfo (
   Supervisor           char(10)             not null,
   Salesman             char(10)             null,
   Schemer              char(10)             null,
   constraint PK_POSTINFO primary key (Supervisor)
)
go

/*==============================================================*/
/* Table: SalesmanInfo                                          */
/*==============================================================*/
create table SalesmanInfo (
   Supervisor           char(10)             null,
   LogoApp              char(10)             null,
   PLogo                char(10)             null
)
go

/*==============================================================*/
/* Table: StaffInfo                                             */
/*==============================================================*/
create table StaffInfo (
   Wno                  char(10)             not null,
   Supervisor           char(10)             null,
   Name                 char(10)             null,
   Post                 char(10)             null,
   Phone                char(10)             null,
   Pay                  char(10)             null,
   constraint PK_STAFFINFO primary key (Wno)
)
go

/*==============================================================*/
/* Table: UserApplyInfo                                         */
/*==============================================================*/
create table UserApplyInfo (
   PicationNumber       char(10)             not null,
   UserId               char(10)             null,
   Date                 char(10)             null,
   Day                  char(10)             null,
   Number               char(10)             null,
   "Add"                char(10)             null,
   price                char(10)             null,
   Logo                 char(10)             null,
   constraint PK_USERAPPLYINFO primary key (PicationNumber)
)
go

/*==============================================================*/
/* Table: UserInfo                                              */
/*==============================================================*/
create table UserInfo (
   UserId               char(10)             not null,
   ApPicationNumber     char(10)             null,
   UserName             char(10)             null,
   BankAccount          char(10)             null,
   Phone                char(10)             null,
   Address              char(10)             null,
   constraint PK_USERINFO primary key (UserId)
)
go

/*==============================================================*/
/* Table: company                                               */
/*==============================================================*/
create table company (
   DNO                  char(10)             null,
   ODment               char(10)             null,
   PDment               char(10)             null,
   Other                char(10)             null
)
go

alter table DepartmentInfo
   add constraint FK_DEPARTME_REFERENCE_STAFFINF foreign key (Wno)
      references StaffInfo (Wno)
go

alter table DepartmentInfo
   add constraint FK_DEPARTME_REFERENCE_POSTINFO foreign key (Supervisor)
      references PostInfo (Supervisor)
go

alter table DepartmentInfo
   add constraint FK_DEPARTME_REFERENCE_SALESMAN foreign key ()
      references SalesmanInfo
go

alter table SalesmanInfo
   add constraint FK_SALESMAN_REFERENCE_POSTINFO foreign key (Supervisor)
      references PostInfo (Supervisor)
go

alter table StaffInfo
   add constraint FK_STAFFINF_REFERENCE_POSTINFO foreign key (Supervisor)
      references PostInfo (Supervisor)
go

alter table StaffInfo
   add constraint FK_STAFFINF_REFERENCE_SALESMAN foreign key ()
      references SalesmanInfo
go

alter table UserInfo
   add constraint FK_USERINFO_REFERENCE_USERAPPL foreign key (ApPicationNumber)
      references UserApplyInfo (PicationNumber)
go

alter table company
   add constraint FK_COMPANY_REFERENCE_DEPARTME foreign key (DNO)
      references DepartmentInfo (DNO)
go



--题目2（10分）：为了保证当用户申请会议，受理标志为 已申请，请设计一个触发器，当用户申请信息表插入数据的时候，可以判断其默认受理标志是否为：已申请，如果是，则无需处理允许正常插入，如果不是则重新个性受理标志为：已申请；
--答：

declare @b nvarchar(100)
create trggier trg_A
    alter
    as inster
    begin
        if(logo='已受理')
        begin
        INSERT INTO UserApplyInfo values @b;
        end
        else
        begin
        declare @a varchar='已申请'
        print @a
        end
    end
        exec dbo.trg_A @b
        
    end

--题目3（10分）：当业务员受理用户申请的时候，需要将当前处理的记录中受理标志修改为：已受理，同时将其它所有状态为：已申请，且会议日期已超过的记录中的受理标志个性为：已过期，请设计一个存储过程来处理此业务；

--答：


declare @b nvarchar(100)
create trggier trg_A
    alter
    as inster
    begin
        if(logo='已受理')
        begin
        INSERT INTO UserApplyInfo values @b;
        end
        else if(logo='已申请')
        begin
       
        end
        else (logo='已过期')
        begin
       
        end
    end
        exec dbo.trg_As @b
        
    end